﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;

using HsTec.Mobile.Green.Model;
using HsTec.Mobile.Green.Util;
using System.Text.RegularExpressions;


namespace HsTec.Mobile.Green.BLL
{
    /// <summary>
    /// 业务逻辑类IndexFormula 的摘要说明。
    /// </summary>
    public class IndexFormulaBLL
    {
        private readonly HsTec.Mobile.Green.DAL.IndexFormulaDAL dal = new HsTec.Mobile.Green.DAL.IndexFormulaDAL();
        public IndexFormulaBLL()
        { }
        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string IF_IDs)
        {
            return dal.Exists(IF_IDs);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(HsTec.Mobile.Green.Model.IndexFormula model)
        {
            return dal.Add(model);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(HsTec.Mobile.Green.Model.IndexFormula model)
        {
            return dal.Update(model);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(string IF_IDs)
        {

            return dal.Delete(IF_IDs);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public HsTec.Mobile.Green.Model.IndexFormula GetModel(int IF_ID)
        {

            return dal.GetModel(IF_ID);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataView GetList(string strWhere)
        {
            return dal.GetList(strWhere);
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataView GetList(int Top, string strWhere, string filedOrder)
        {
            return dal.GetList(Top, strWhere, filedOrder);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<HsTec.Mobile.Green.Model.IndexFormula> GetModelList(string strWhere)
        {
            DataView dv = dal.GetList(strWhere);
            return DataTableToList(dv.Table);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<HsTec.Mobile.Green.Model.IndexFormula> DataTableToList(DataTable dt)
        {
            List<HsTec.Mobile.Green.Model.IndexFormula> modelList = new List<HsTec.Mobile.Green.Model.IndexFormula>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                HsTec.Mobile.Green.Model.IndexFormula model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new HsTec.Mobile.Green.Model.IndexFormula();
                    if (dt.Rows[n]["IF_ID"].ToString() != "")
                    {
                        model.IF_ID = int.Parse(dt.Rows[n]["IF_ID"].ToString());
                    }
                    model.IndexName = dt.Rows[n]["IndexName"].ToString();
                    model.FormulaNo = dt.Rows[n]["FormulaNo"].ToString();
                    model.Formula = dt.Rows[n]["Formula"].ToString();
                    if (dt.Rows[n]["FormulaParent"].ToString() != "")
                    {
                        model.FormulaParent = int.Parse(dt.Rows[n]["FormulaParent"].ToString());
                    }
                    if (dt.Rows[n]["OutOf"].ToString() != "")
                    {
                        model.OutOf = int.Parse(dt.Rows[n]["OutOf"].ToString());
                    }
                    model.FormulaSQL = dt.Rows[n]["FormulaSQL"].ToString();
                    model.YearState = dt.Rows[n]["YearState"].ToString();
                    if (dt.Rows[n]["FormulaChildCount"].ToString() != "")
                    {
                        model.FormulaChildCount = int.Parse(dt.Rows[n]["FormulaChildCount"].ToString());
                    }
                    model.IndexSQL = dt.Rows[0]["IndexSQL"].ToString();
                    modelList.Add(model);
                }
            }
            return modelList;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataView GetAllList()
        {
            return GetList("");
        }

        #endregion  成员方法

        /// <summary>
        /// 获取节点下所有的孩子节点
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<IndexFormula> GetModelListByID(int uid)
        {
            return this.GetModelList("FormulaParent=" + uid);
        }

        /// <summary>
        /// 根据公式计算 指标值
        /// </summary>
        /// <param name="sYear"></param>
        /// <param name="sArea"></param>
        /// <param name="stype"></param>
        public DataView GeneratedFormula(string sYear, string sArea, ref string sRtn)
        {
            //定义用于最终输出的DataTable
            DataTable dt = new DataTable();
            dt.Columns.Add("Area_ID", typeof(string));
            dt.Columns.Add("Years", typeof(string));
            dt.Columns.Add("Area_Name", typeof(string));
            DataColumn[] dc = new DataColumn[1];
            dc[0] = dt.Columns["Area_ID"];
            dt.PrimaryKey = dc;
            string sql = String.Empty;
            //字典中存储 地址 和 对应的各项值
            Dictionary<string, Dictionary<string, string>> dicItems = new Dictionary<string, Dictionary<string, string>>();
            //1.从表中取出所有的字段值
            List<IndexFormula> listFields = this.GetModelList("FormulaParent = 1");
            foreach (IndexFormula ifa in listFields)
            {
                dt.Columns.Add(ifa.IndexName, typeof(string));
                dt.Columns.Add(ifa.IndexName + "Last", typeof(string));
                //省公司信息
                dt.Columns.Add(ifa.IndexName + "Sgs", typeof(string));
                dt.Columns.Add(ifa.IndexName + "SgsLast", typeof(string));
                //查询获取 地区 月份 信息相对应的 字段值

                //处理sYear
                sql = "select sum(cast( " + ifa.Formula + " as decimal(20, 4))),Area_id from " + ifa.FormulaNo + " where Years <='" + sYear + "' and Years>='" + sYear.Substring(0, 4) + "01" + "'  and Area_id in (" + sArea + ") group by Area_id ";
                DataView fielddv = SQLHelper.ExecuteDataView(sql);
                for (int i = 0; i < fielddv.Count; i++)
                {
                    //显示 地区 <2G ,1111.11>
                    if (!dicItems.ContainsKey(fielddv[i][1].ToString()))
                    {
                        dicItems.Add(fielddv[i][1].ToString(), new Dictionary<string, string>() { { ifa.IndexName, fielddv[i][0].ToString() } });
                    }
                    else
                    {
                        dicItems[fielddv[i][1].ToString()].Add(ifa.IndexName, fielddv[i][0].ToString());
                    }
                }
                //--------------------------需要检查数据的完整性 即 有可能会出现有的地区部分数据不完整 需要提示 未导入数据的地市 的相关数据信息




                //-----------------------------------------------------------------------------------------------------------------------------
                //处理sYear-1 上一年
                string sTmpYear = DateTime.Parse(sYear.Substring(0, 4) + "-" + sYear.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");

                #region old
                //对上一年做判断 如果是2009需要单独进行处理
                //if (sTmpYear.Substring(0, 4) == "2009")
                //{
                //    if (ifa.IndexName == "2G" || ifa.IndexName == "3G")
                //        sql = "select sum(cast( PDCH as decimal(20, 4)))/2,Area_id from tb_ForecastBusiness where  Years <='" + sTmpYear + "' and Years>='" + sTmpYear.Substring(0, 4) + "01" + "' and Area_ID in (" + sArea + ") group by Area_ID ";
                //    else if(ifa.IndexName=="话务量"||ifa.IndexName=="TD话务量")
                //        sql = "select sum(cast( FactBusiness as decimal(20, 4)))/2,Area_id from tb_ForecastBusiness where  Years <='" + sTmpYear + "' and Years>='" + sTmpYear.Substring(0, 4) + "01" + "' and Area_ID in (" + sArea + ") group by Area_ID ";
                //}
                //else
                //{
                //sql = "select sum(cast( " + ifa.Formula + " as decimal(20, 4))),Area_id from " + ifa.FormulaNo + " where  Years <='" + sTmpYear + "' and Years>='" + sTmpYear.Substring(0, 4) + "01" + "' and Area_id in (" + sArea + ") group by Area_id ";
                //}
                #endregion

                sql = "select sum(cast( " + ifa.Formula + " as decimal(20, 4))),Area_id from " + ifa.FormulaNo + " where  Years <='" + sTmpYear + "' and Years>='" + sTmpYear.Substring(0, 4) + "01" + "' and Area_id in (" + sArea + ") group by Area_id ";
                fielddv = SQLHelper.ExecuteDataView(sql);
                for (int i = 0; i < fielddv.Count; i++)
                {
                    //显示 地区 <2G ,1111.11>
                    dicItems[fielddv[i][1].ToString()].Add(ifa.IndexName + "Last", fielddv[i][0].ToString());
                }
            }
            //2.处理所有计算值
            List<IndexFormula> listMiddle = this.GetModelList("FormulaParent <> 1 order by IsProcedure ");
            foreach (IndexFormula ifa in listMiddle)
            {
                dt.Columns.Add(ifa.IndexName, typeof(string));
                dt.Columns.Add(ifa.IndexName + "Last", typeof(string));
                foreach (string sAreaid in dicItems.Keys)
                {
                    string sFormula = ifa.Formula;
                    string sqlvalue = String.Empty;
                    string[] ary = ifa.FormulaNo.Split(',');
                    //定义字段 并赋值
                    //处理sYear
                    foreach (string tmp in ary)
                    {
                        sqlvalue += "declare " + tmp + " decimal(20, 4);";
                        sqlvalue += "select  " + tmp + "= " + dicItems[sAreaid][tmp.Replace("@", "")] + ";";
                    }
                    sqlvalue += "select " + sFormula + ";";
                    string sValue = SQLHelper.ExecuteString(sqlvalue);
                    dicItems[sAreaid].Add(ifa.IndexName, sValue);
                    //处理sYear-1
                    sqlvalue = String.Empty;
                    int iFlag = 0;
                    foreach (string tmp in ary)
                    {
                        if (tmp.IndexOf("Last") > 0)
                        {
                            iFlag = 1;
                            continue;
                        }
                        sqlvalue += "declare " + tmp + " decimal(20, 4);";
                        sqlvalue += "select  " + tmp + "= " + dicItems[sAreaid][tmp.Replace("@", "") + "Last"] + ";";
                    }
                    if (iFlag == 0)
                    {
                        sqlvalue += "select " + sFormula + ";";
                        sValue = SQLHelper.ExecuteString(sqlvalue);
                        dicItems[sAreaid].Add(ifa.IndexName + "Last", sValue);
                    }
                }
            }
            //3.返回数据表格
            foreach (string sAreaID in dicItems.Keys)
            {
                DataRow dr = dt.NewRow();
                dr["Area_ID"] = sAreaID;
                dr["Area_Name"] = SQLHelper.ExecuteString("select [Name] from Area where ID=" + sAreaID);
                dr["Years"] = sYear;
                foreach (string sField in dicItems[sAreaID].Keys)
                {
                    dr[sField] = dicItems[sAreaID][sField];
                }
                dt.Rows.Add(dr);
            }
            return dt.DefaultView;
        }
        public DataView GeneratedFormulaNew(string sYear, string sArea, string items, ref string sRtn)
        {
            FormulaAnalyse fa = new FormulaAnalyse();
            DataTable dt = new DataTable();
            dt.Columns.Add("Area_ID", typeof(string));
            dt.Columns.Add("Years", typeof(string));
            dt.Columns.Add("Area_Name", typeof(string));
            //设置主键
            DataColumn[] dc = new DataColumn[1];
            dc[0] = dt.Columns["Area_ID"];
            dt.PrimaryKey = dc;
            //查询项
            foreach (string item in items.Split(','))
            {
                string[] ary = item.Split('^');
                dt.Columns.Add(ary[0], typeof(string));
            }
            foreach (string sAreaID in sArea.Split(','))
            {
                string[] ary = sAreaID.Split('^');
                DataRow dr = dt.NewRow();
                dr["Area_ID"] = ary[0];
                dr["Area_Name"] = ary[1];
                dr["Years"] = sYear;
                foreach (string item in items.Split(','))
                {
                    string[] aryitem = item.Split('^');
                    dr[aryitem[0]] = fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and Years='" + sYear + "'");
                }
                dt.Rows.Add(dr);
            }
            return dt.DefaultView;
        }

        /// <summary>
        /// 查询自定义表中的字段信息
        /// </summary>
        /// <param name="sAreas">区域信息</param>
        /// <param name="sStartYears">起始日期</param>
        /// <param name="sEndYears">终止日期</param>
        /// <param name="sFields">字段列表</param>
        /// <param name="sType">0:默认;1:同比;2:环比;</param>
        /// <param name="bTotal">是否进行累计</param>
        /// <returns></returns>
        public DataView QueryTableField(string sAreas, string sStartYears, string sEndYears, string sFields, string sTable, string sType, bool bTotal)
        {
            string tabletmp = sTable.Split('|')[1];
            if (tabletmp.IndexOf("tb") == 0)
            {
                sTable = tabletmp;
            }
            else
            {
                sTable = "rt_" + tabletmp;
            }
            string sql = String.Empty;
            string sAreaIDs = String.Empty;
            string[] aryArea = sFields.Split(',');
            string[] aryList = { null, null };
            string sQueryField = String.Empty;
            string sQueryFieldTotal = String.Empty;
            //处理同比时间
            string sLastStartYears = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");
            string sLastEndYears = DateTime.Parse(sEndYears.Substring(0, 4) + "-" + sEndYears.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");
            //处理环比时间
            //------------修正环比算法-------------//
            //string sLastStartMonth = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01").AddMonths(-1).ToString("yyyyMM");
            //string sLastEndMonth = DateTime.Parse(sEndYears.Substring(0, 4) + "-" + sEndYears.Substring(4, 2) + "-01").AddMonths(-1).ToString("yyyyMM");
            int hbmonth = Math.Abs((int.Parse(sEndYears.Substring(0, 4)) - int.Parse(sStartYears.Substring(0, 4))) * 12 + int.Parse(sEndYears.Substring(4, 2)) - int.Parse(sStartYears.Substring(4, 2)));
            string sLastEndMonth = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01").AddMonths(-1).ToString("yyyyMM");
            string sLastStartMonth = DateTime.Parse(sLastEndMonth.Substring(0, 4) + "-" + sLastEndMonth.Substring(4, 2) + "-01").AddMonths(-hbmonth).ToString("yyyyMM");
            //------------修正环比算法-------------//
            sQueryField = "[Years] as 年月,Area.[Name] as 地区";
            sQueryFieldTotal = ",max(Area.[Name]) as 地区";
            foreach (string tmp in aryArea)
            {
                aryList = tmp.Split('^');
                sQueryField += ",[" + aryList[1] + "] as [" + aryList[0] + "]";
                sQueryFieldTotal += ",sum([" + aryList[1] + "]) as [" + aryList[0] + "]";
            }
            foreach (string tmp in sAreas.Split(','))
            {
                aryList = tmp.Split('^');
                sAreaIDs += String.IsNullOrEmpty(sAreaIDs) ? aryList[0] : "," + aryList[0];
            }
            string sWhereAid = String.Empty;
            if (!String.IsNullOrEmpty(sAreaIDs))
            {
                sWhereAid = "and Area_ID in (" + sAreaIDs + ")";
            }
            //判断是否是累计状态
            if (!bTotal)
            {
                //判断是同比 环比 还是默认
                switch (sType)
                {
                    case "0":
                        sql = "select " + sQueryField + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where Years>='" + sStartYears + "'and Years<='" + sEndYears + "' " + sWhereAid +
                            "order by Years";
                        break;
                    case "1":
                        sql = "select " + sQueryField + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where ((Years>='" + sStartYears + "'and Years<='" + sEndYears + "') or (Years>='" + sLastStartYears + "'and Years<='" + sLastEndYears + "')) " + sWhereAid +
                            "order by Area_ID";
                        break;
                    case "2":
                        sql = "select " + sQueryField + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where ((Years>='" + sStartYears + "'and Years<='" + sEndYears + "') or (Years>='" + sLastStartMonth + "'and Years<='" + sLastEndMonth + "')) " + sWhereAid +
                            "order by Area_ID";
                        break;
                }
            }
            else
            {
                switch (sType)
                {
                    case "0":
                        sql = "select '" + sStartYears + "至" + sEndYears + "' as 年月" + sQueryFieldTotal + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where Years>='" + sStartYears + "'and Years<='" + sEndYears + "' " + sWhereAid +
                            "group by Area_ID";
                        break;
                    case "1":
                        sql = "select '" + sStartYears + "至" + sEndYears + "' as 年月" + sQueryFieldTotal + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where Years>='" + sStartYears + "'and Years<='" + sEndYears + "' " + sWhereAid +
                            "group by Area_ID " +
                            "union " +
                            "select '" + sLastStartYears + "至" + sLastEndYears + "' as 年月" + sQueryFieldTotal + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where Years>='" + sLastStartYears + "'and Years<='" + sLastEndYears + "' " + sWhereAid +
                            "group by Area_ID ";
                        break;
                    case "2":
                        sql = "select '" + sStartYears + "至" + sEndYears + "' as 年月" + sQueryFieldTotal + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where Years>='" + sStartYears + "'and Years<='" + sEndYears + "' " + sWhereAid +
                            "group by Area_ID " +
                            "union " +
                            "select '" + sLastStartMonth + "至" + sLastEndMonth + "' as 年月" + sQueryFieldTotal + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where Years>='" + sLastStartMonth + "'and Years<='" + sLastEndMonth + "' " + sWhereAid +
                            "group by Area_ID";
                        break;
                }
            }
            return SQLHelper.ExecuteDataView(sql);
        }

        /// <summary>
        /// 查询公式中的字段信息
        /// </summary>
        /// <param name="sArea"></param>
        /// <param name="sStartYears"></param>
        /// <param name="sEndYears"></param>
        /// <param name="sField"></param>
        /// <param name="sTable"></param>
        /// <param name="sType"></param>
        /// <param name="bTotal"></param>
        /// <returns></returns>
        public DataView QueryFormula(string sArea, string sStartYears, string sEndYears, string sFields, string sTable, string sType, bool bTotal)
        {
            int iNum = 0;
            FormulaAnalyse fa = new FormulaAnalyse();
            DataTable dt = new DataTable();
            dt.Columns.Add("序号", typeof(string));
            //dt.Columns.Add("Area_ID", typeof(string));
            dt.Columns.Add("年月", typeof(string));
            dt.Columns.Add("地区", typeof(string));
            //设置主键
            DataColumn[] dc = new DataColumn[1];
            dc[0] = dt.Columns["序号"];
            dt.PrimaryKey = dc;
            //查询项
            foreach (string item in sFields.Split(','))
            {
                string[] ary = item.Split('^');
                dt.Columns.Add(ary[0], typeof(string));
            }
            //处理日期型数据
            DateTime dStartYears = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01");
            DateTime dEndYears = DateTime.Parse(sEndYears.Substring(0, 4) + "-" + sEndYears.Substring(4, 2) + "-01");
            //处理同比时间
            DateTime sLastStartYears = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01").AddYears(-1);
            DateTime sLastEndYears = DateTime.Parse(sEndYears.Substring(0, 4) + "-" + sEndYears.Substring(4, 2) + "-01").AddYears(-1);
            //处理环比时间
            //DateTime sLastStartMonth = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01").AddMonths(-1);
            //DateTime sLastEndMonth = DateTime.Parse(sEndYears.Substring(0, 4) + "-" + sEndYears.Substring(4, 2) + "-01").AddMonths(-1);
            int hbmonth = Math.Abs((int.Parse(sEndYears.Substring(0, 4)) - int.Parse(sStartYears.Substring(0, 4))) * 12 + int.Parse(sEndYears.Substring(4, 2)) - int.Parse(sStartYears.Substring(4, 2)));
            DateTime sLastEndMonth = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01").AddMonths(-1);
            DateTime sLastStartMonth = DateTime.Parse(sLastEndMonth.ToString("yyyyMM").Substring(0, 4) + "-" + sLastEndMonth.ToString("yyyyMM").Substring(4, 2) + "-01").AddMonths(-hbmonth);
            //------------修正环比算法-------------//
            //判断是否是累计状态
            if (!bTotal)
            {
                iNum = 0;
                switch (sType)
                {
                    case "0":

                        foreach (string sAreaID in sArea.Split(','))
                        {
                            for (DateTime dateTime = dStartYears; dateTime <= dEndYears; )
                            {
                                string[] ary = sAreaID.Split('^');
                                DataRow dr = dt.NewRow();
                                iNum++;
                                dr["序号"] = iNum;
                                //dr["Area_ID"] = ary[0];
                                dr["地区"] = ary[1];
                                dr["年月"] = dateTime.ToString("yyyyMM");
                                foreach (string item in sFields.Split(','))
                                {
                                    string[] aryitem = item.Split('^');
                                    dr[aryitem[0]] = fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and Years='" + dateTime.ToString("yyyyMM") + "'");
                                }
                                dt.Rows.Add(dr);
                                dateTime = dateTime.AddMonths(1);
                            }
                        }
                        break;
                    case "1":
                        foreach (string sAreaID in sArea.Split(','))
                        {
                            for (DateTime dateTime = dStartYears; dateTime <= dEndYears; )
                            {
                                string[] ary = sAreaID.Split('^');
                                DataRow dr = dt.NewRow();
                                iNum++;
                                dr["序号"] = iNum;
                                //dr["Area_ID"] = ary[0];
                                dr["地区"] = ary[1];
                                dr["年月"] = dateTime.ToString("yyyyMM");
                                foreach (string item in sFields.Split(','))
                                {
                                    string[] aryitem = item.Split('^');
                                    dr[aryitem[0]] = fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and Years='" + dateTime.ToString("yyyyMM") + "'");
                                }
                                dt.Rows.Add(dr);
                                dateTime = dateTime.AddMonths(1);
                            }
                            for (DateTime dateTime = sLastStartYears; dateTime <= sLastEndYears; )
                            {
                                string[] ary = sAreaID.Split('^');
                                DataRow dr = dt.NewRow();
                                iNum++;
                                dr["序号"] = iNum;
                                //dr["Area_ID"] = ary[0];
                                dr["地区"] = ary[1];
                                dr["年月"] = dateTime.ToString("yyyyMM");
                                foreach (string item in sFields.Split(','))
                                {
                                    string[] aryitem = item.Split('^');
                                    dr[aryitem[0]] = fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and Years='" + dateTime.ToString("yyyyMM") + "'");
                                }
                                dt.Rows.Add(dr);
                                dateTime = dateTime.AddMonths(1);
                            }
                        }
                        break;
                    case "2":
                        foreach (string sAreaID in sArea.Split(','))
                        {
                            for (DateTime dateTime = dStartYears; dateTime <= dEndYears; )
                            {
                                string[] ary = sAreaID.Split('^');
                                DataRow dr = dt.NewRow();
                                iNum++;
                                dr["序号"] = iNum;
                                //dr["Area_ID"] = ary[0];
                                dr["地区"] = ary[1];
                                dr["年月"] = dateTime.ToString("yyyyMM");
                                foreach (string item in sFields.Split(','))
                                {
                                    string[] aryitem = item.Split('^');
                                    dr[aryitem[0]] = fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and Years='" + dateTime.ToString("yyyyMM") + "'");
                                }
                                dt.Rows.Add(dr);
                                dateTime = dateTime.AddMonths(1);
                            }
                            //if (true)
                            for (DateTime dateTime = sLastStartMonth; dateTime <= sLastEndMonth; )
                            {
                                string[] ary = sAreaID.Split('^');
                                DataRow dr = dt.NewRow();
                                iNum++;
                                dr["序号"] = iNum;
                                //dr["Area_ID"] = ary[0];
                                dr["地区"] = ary[1];
                                dr["年月"] = dateTime.ToString("yyyyMM");
                                foreach (string item in sFields.Split(','))
                                {
                                    string[] aryitem = item.Split('^');
                                    dr[aryitem[0]] = fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and Years='" + dateTime.ToString("yyyyMM") + "'");
                                }
                                dt.Rows.Add(dr);
                                dateTime = dateTime.AddMonths(1);
                            }
                        }
                        break;
                }
            }
            else
            {
                iNum = 0;
                switch (sType)
                {
                    case "0":
                        foreach (string sAreaID in sArea.Split(','))
                        {
                            string[] ary = sAreaID.Split('^');
                            DataRow dr = dt.NewRow();
                            iNum++;
                            dr["序号"] = iNum;
                            //dr["Area_ID"] = ary[0];
                            dr["地区"] = ary[1];
                            dr["年月"] = dStartYears.ToString("yyyyMM") + "至" + dEndYears.ToString("yyyyMM");
                            foreach (string item in sFields.Split(','))
                            {
                                string[] aryitem = item.Split('^');

                                dr[aryitem[0]] = Decimal.Parse(String.IsNullOrEmpty(dr[aryitem[0]].ToString()) ? "0" : dr[aryitem[0]].ToString()) + Decimal.Parse(fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and (Years>='" + sStartYears + "' and Years<='" + sEndYears + "')"));
                            }
                            dt.Rows.Add(dr);
                        }
                        break;
                    case "1":
                        foreach (string sAreaID in sArea.Split(','))
                        {
                            string[] ary = sAreaID.Split('^');
                            DataRow dr = dt.NewRow();
                            iNum++;
                            dr["序号"] = iNum;
                            //dr["Area_ID"] = ary[0];
                            dr["地区"] = ary[1];
                            dr["年月"] = dStartYears.ToString("yyyyMM") + "至" + dEndYears.ToString("yyyyMM");
                            foreach (string item in sFields.Split(','))
                            {
                                string[] aryitem = item.Split('^');
                                dr[aryitem[0]] = Decimal.Parse(String.IsNullOrEmpty(dr[aryitem[0]].ToString()) ? "0" : dr[aryitem[0]].ToString()) + Decimal.Parse(fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and (Years>='" + sStartYears + "' and Years<='" + sEndYears + "')"));
                            }
                            dt.Rows.Add(dr);
                            dr = dt.NewRow();
                            iNum++;
                            dr["序号"] = iNum;
                            //dr["Area_ID"] = ary[0];
                            dr["地区"] = ary[1];
                            dr["年月"] = sLastStartYears.ToString("yyyyMM") + "至" + sLastEndYears.ToString("yyyyMM");
                            foreach (string item in sFields.Split(','))
                            {
                                string[] aryitem = item.Split('^');
                                dr[aryitem[0]] = Decimal.Parse(String.IsNullOrEmpty(dr[aryitem[0]].ToString()) ? "0" : dr[aryitem[0]].ToString()) + Decimal.Parse(fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and (Years>='" + sLastStartYears.ToString("yyyyMM") + "' and Years<='" + sLastEndYears.ToString("yyyyMM") + "')"));
                            }
                            dt.Rows.Add(dr);
                        }
                        break;
                    case "2":
                        foreach (string sAreaID in sArea.Split(','))
                        {

                            string[] ary = sAreaID.Split('^');
                            DataRow dr = dt.NewRow();
                            iNum++;
                            dr["序号"] = iNum;
                            //dr["Area_ID"] = ary[0];
                            dr["地区"] = ary[1];
                            dr["年月"] = dStartYears.ToString("yyyyMM") + "至" + dEndYears.ToString("yyyyMM");
                            foreach (string item in sFields.Split(','))
                            {
                                string[] aryitem = item.Split('^');
                                dr[aryitem[0]] = Decimal.Parse(String.IsNullOrEmpty(dr[aryitem[0]].ToString()) ? "0" : dr[aryitem[0]].ToString()) + Decimal.Parse(fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and (Years>='" + sStartYears + "' and Years<='" + sEndYears + "')"));
                            }
                            dt.Rows.Add(dr);

                            dr = dt.NewRow();
                            iNum++;
                            dr["序号"] = iNum;
                            //dr["Area_ID"] = ary[0];
                            dr["地区"] = ary[1];
                            dr["年月"] = sLastStartMonth.ToString("yyyyMM") + "至" + sLastEndMonth.ToString("yyyyMM");
                            foreach (string item in sFields.Split(','))
                            {
                                string[] aryitem = item.Split('^');
                                dr[aryitem[0]] = Decimal.Parse(String.IsNullOrEmpty(dr[aryitem[0]].ToString()) ? "0" : dr[aryitem[0]].ToString()) + Decimal.Parse(fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and (Years>='" + sLastStartMonth.ToString("yyyyMM") + "' and Years<='" + sLastEndMonth.ToString("yyyyMM") + "')"));
                            }
                            dt.Rows.Add(dr);

                        }
                        break;
                }
            }
            return dt.DefaultView;
        }

        public DataView QueryQuantity(string sArea, string sStartYears, string sEndYears, string sFields, string sTable, string sType, bool bTotal, string sLastYears)
        {
            int iNum = 0;
            FormulaAnalyse fa = new FormulaAnalyse();
            DataTable dt = new DataTable();
            dt.Columns.Add("序号", typeof(string));
            //dt.Columns.Add("Area_ID", typeof(string));
            dt.Columns.Add("年月", typeof(string));
            dt.Columns.Add("地区", typeof(string));
            //设置主键
            DataColumn[] dc = new DataColumn[1];
            dc[0] = dt.Columns["序号"];
            dt.PrimaryKey = dc;
            //查询项
            foreach (string item in sFields.Split(','))
            {
                string[] ary = item.Split('^');
                dt.Columns.Add(ary[0], typeof(string));
            }
            //处理日期
            DateTime dStartYears = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01");
            DateTime dEndYears = DateTime.Parse(sEndYears.Substring(0, 4) + "-" + sEndYears.Substring(4, 2) + "-01");
            //判断是否是累计状态
            if (!bTotal)
            {
                iNum = 0;
                foreach (string sAreaID in sArea.Split(','))
                {
                    for (DateTime dateTime = dStartYears; dateTime <= dEndYears; )
                    {
                        string[] ary = sAreaID.Split('^');
                        DataRow dr = dt.NewRow();
                        iNum++;
                        dr["序号"] = iNum;
                        //dr["Area_ID"] = ary[0];
                        dr["地区"] = ary[1];
                        dr["年月"] = dateTime.ToString("yyyyMM");
                        string dateTimeLast = sLastYears + dateTime.ToString("yyyyMM").Substring(4, 2);
                        foreach (string item in sFields.Split(','))
                        {
                            string[] aryitem = item.Split('^');
                            try
                            {
                                dr[aryitem[0]] = Decimal.Parse(fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and Years='" + dateTime.ToString("yyyyMM") + "'")) / Decimal.Parse(fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and Years='" + dateTimeLast + "'")) - 1;
                            }
                            catch
                            {
                                dr[aryitem[0]] = "0";
                            }
                        }
                        dt.Rows.Add(dr);
                        dateTime = dateTime.AddMonths(1);
                    }
                }
            }
            else
            {
                iNum = 0;
                foreach (string sAreaID in sArea.Split(','))
                {

                    string[] ary = sAreaID.Split('^');
                    DataRow dr = dt.NewRow();
                    iNum++;
                    dr["序号"] = iNum;
                    //dr["Area_ID"] = ary[0];
                    dr["地区"] = ary[1];
                    dr["年月"] = dStartYears.ToString("yyyyMM") + "至" + dEndYears.ToString("yyyyMM");
                    foreach (string item in sFields.Split(','))
                    {
                        string[] aryitem = item.Split('^');
                        dr[aryitem[0]] = Decimal.Parse(fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and (Years>='" + dStartYears.ToString("yyyyMM") + "' and Years<='" + dEndYears.ToString("yyyyMM") + "')")) / Decimal.Parse(fa.Analyse(aryitem[1], " and Area_ID='" + ary[0] + "' and (Years>='" + sLastYears + sStartYears.Substring(4, 2) + "' and Years<='" + sLastYears + sEndYears.Substring(4, 2) + "')")) - 1;
                    }
                    dt.Rows.Add(dr);
                }
                DataView tmpDv = dt.DefaultView;
                //计算指标项字段的分数
                Dictionary<string, string> listSort = new Dictionary<string, string>();
                foreach (string item in sFields.Split(','))
                {
                    string[] ary = item.Split('^');
                    tmpDv.Sort = ary[0];
                    //获取字段的考试分数和类型
                    DataView filedDv = SQLHelper.ExecuteDataView("select OutOf,FormulaChildCount from tb_IndexFormula where IndexName = '" + ary[0] + "'");
                    string sCalScort = filedDv[0]["OutOf"].ToString();
                    string sCalType = filedDv[0]["FormulaChildCount"].ToString();
                    for (int i = 0; i < tmpDv.Count; i++)
                    {
                        listSort.Add(tmpDv[i]["序号"].ToString(), tmpDv[i][ary[0]].ToString());
                    }
                    string max = String.Empty;
                    string min = String.Empty;
                    Dictionary<string, string> tmpSort = new Dictionary<string, string>(listSort); ;

                    switch (sCalType)
                    {
                        case "1"://得分计算类型   线性
                            max = tmpDv[tmpDv.Count - 1][ary[0]].ToString();
                            foreach (string key in tmpSort.Keys)
                            {
                                if (Double.Parse(tmpSort[key]) < 0.6)
                                    listSort[key] = "0";
                                else
                                    listSort[key] = (Double.Parse(sCalScort) / Double.Parse(max) * Double.Parse(tmpSort[key])).ToString();
                            }
                            //回填分数
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                dt.Rows[i][ary[0]] = listSort[dt.Rows[i]["序号"].ToString()];
                            }
                            break;
                        case "2"://得分计算类型   比高
                            min = tmpDv[0][ary[0]].ToString();
                            max = tmpDv[tmpDv.Count - 1][ary[0]].ToString();
                            foreach (string key in tmpSort.Keys)
                            {
                                if (tmpSort[key] == max)
                                    listSort[key] = "0";
                                else
                                    listSort[key] = (Double.Parse(sCalScort) / Double.Parse(min) * Double.Parse(tmpSort[key])).ToString();
                            }
                            //回填分数
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                dt.Rows[i][ary[0]] = listSort[dt.Rows[i]["序号"].ToString()];
                            }
                            break;
                    }
                }
            }
            return dt.DefaultView;
        }



        public DataView GetAreaList()
        {
            return SQLHelper.ExecuteDataView("select [ID],[Name] from Area where ParentID='10' order by ID");
        }
        public DataView GetAreaList(string sAreaId)
        {
            if (!String.IsNullOrEmpty(sAreaId) && sAreaId!="0")
                return SQLHelper.ExecuteDataView("select * from Area where ParentID=10 and ID='" + sAreaId + "'");
            else
                return GetAreaList();
        }
        public DataView GetIndicatorList(string sArea, string sStartYear, string sEndYear)
        {
            //1. 获取指标公式 并生成对应的SQL语句
            List<string> listIndicator = new List<string>();
            //1.1 获取所有公式表中的指标公式项
            DataView dvFormula = SQLHelper.ExecuteDataView("select * from tb_IndexFormula where FormulaParent=3 and YearState=1 order by IsProcedure");
            for (int i = 0; i < dvFormula.Count; i++)
            {
                //1.2 分析每一个指标项公式
                string sFormulaSQL = dvFormula[i]["FormulaSQL"].ToString();
                string tmpFormula = sFormulaSQL;
                string tmpSQL = String.Empty;
                string tmpData = String.Empty;
                string[] aryList = { null, null };
                string regexSumStr = @"sum\(\w*!\w*\)";
                string regexAvgStr = @"avg\(\w*!\w*\)";
                string regexFiled = @"\w*!\w*";
                Regex rSum = new Regex(regexSumStr, RegexOptions.None);
                Regex rAvg = new Regex(regexAvgStr, RegexOptions.None);
                Regex rField = new Regex(regexFiled, RegexOptions.None);
                Match mFiled = null;
                //获取所有的匹配项
                MatchCollection mcs = rSum.Matches(tmpFormula);
                foreach (Match mc in mcs)
                {
                    mFiled = rField.Match(mc.ToString());
                    aryList = mFiled.ToString().Split('!');
                    //TODO 关于数据如果为空如何处理
                    tmpSQL = "select sum(cast(case(cast([" + aryList[1] + "] as varchar(200))) when '' then '0' else [" + aryList[1] + "] end as decimal(20, 4))) from [" + aryList[0] + "] where (1=1) sWhere";
                    tmpFormula = tmpFormula.Replace(mc.ToString(), "(" + tmpSQL + ")");
                }
                mcs = rAvg.Matches(tmpFormula);
                foreach (Match mc in mcs)
                {
                    mFiled = rField.Match(mc.ToString());
                    aryList = mFiled.ToString().Split('!');
                    tmpSQL = "select avg(cast(case(cast([" + aryList[1] + "] as varchar(200))) when '' then '0' else [" + aryList[1] + "] end as decimal(20, 4))) from [" + aryList[0] + "] where (1=1) sWhere";
                    tmpFormula = tmpFormula.Replace(mc.ToString(), "(" + tmpSQL + ")");
                }
                mcs = rField.Matches(tmpFormula);
                foreach (Match mc in mcs)
                {
                    aryList = mc.ToString().Split('!');
                    tmpSQL = "select sum(cast(case(cast([" + aryList[1] + "] as varchar(200))) when '' then '0' else [" + aryList[1] + "] end as decimal(20, 4))) from [" + aryList[0] + "] where (1=1) sWhere";
                    tmpFormula = tmpFormula.Replace(mc.ToString(), "(" + tmpSQL + ")");
                }
                listIndicator.Add(tmpFormula);
            }
            DataView areaList = SQLHelper.ExecuteDataView("select [ID],[Name],[ParentID] from Area where ParentID=10 order by ID");
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(string));
            dt.Columns.Add("Years", typeof(string));
            dt.Columns.Add("AreaName", typeof(string));
            dt.Columns.Add("DX1", typeof(string));
            dt.Columns.Add("DX2", typeof(string));
            dt.Columns.Add("DX3", typeof(string));
            dt.Columns.Add("DJ1", typeof(string));
            dt.Columns.Add("DJ2", typeof(string));
            dt.Columns.Add("DJ3", typeof(string));
            dt.Columns.Add("DY1", typeof(string));
            dt.Columns.Add("DY2", typeof(string));
            dt.Columns.Add("DY3", typeof(string));
            string sqlIndiatos = String.Empty;
            DataView tmpDv = new DataView();
            DataView tmpDvLast = new DataView();
            DateTime dLastStartYears = DateTime.Parse(sStartYear.Substring(0, 4) + "-" + sEndYear.Substring(4, 2) + "-01").AddYears(-1);
            DateTime dLastEndYears = DateTime.Parse(sStartYear.Substring(0, 4) + "-" + sEndYear.Substring(4, 2) + "-01").AddYears(-1);
            //输出各地市信息
            for (int i = 0; i < areaList.Count; i++)
            {
                sqlIndiatos = "select '" + i + "'as ID," +
                    "'" + sStartYear + "至" + sEndYear + "' as Years," +
                    "(select " + listIndicator[0].Replace("sWhere", " and Area_ID='" + areaList[i][0] + "' and Years>='" + sStartYear + "' and Years<='" + sEndYear + "'") + ") as DX1," +
                    "(select EnergyChange from tb_ChangeRateInfo where Area_ID='" + areaList[i][0] + "' and Years=substring('" + sStartYear + "',1,4)) as DX2," +
                    "(select " + listIndicator[1].Replace("sWhere", " and Area_ID='" + areaList[i][0] + "' and Years>='" + sStartYear + "' and Years<='" + sEndYear + "'") + ") as DJ1," +
                    "(select BaseChange from tb_ChangeRateInfo where Area_ID='" + areaList[i][0] + "' and Years=substring('" + sStartYear + "',1,4)) as DJ2," +
                    "(select " + listIndicator[2].Replace("sWhere", " and Area_ID='" + areaList[i][0] + "' and Years>='" + sStartYear + "' and Years<='" + sEndYear + "'") + ") as DY1," +
                    "(select TrafficChange from tb_ChangeRateInfo where Area_ID='" + areaList[i][0] + "' and Years=substring('" + sStartYear + "',1,4)) as DY2";
                tmpDv = SQLHelper.ExecuteDataView(sqlIndiatos);
                sqlIndiatos = "select '" + i + "'as ID," +
                    "'" + sStartYear + "至" + sEndYear + "'," +
                    "(select " + listIndicator[0].Replace("sWhere", " and Area_ID='" + areaList[i][0] + "' and Years>='" + dLastStartYears.ToString("yyyyMM") + "' and Years<='" + dLastEndYears.ToString("yyyyMM") + "'") + ") as DX1," +
                    "(select EnergyChange from tb_ChangeRateInfo where Area_ID='" + areaList[i][0] + "' and Years=substring('" + dLastStartYears.ToString("yyyyMM") + "',1,4)) as DX2," +
                    "(select " + listIndicator[1].Replace("sWhere", " and Area_ID='" + areaList[i][0] + "' and Years>='" + dLastStartYears.ToString("yyyyMM") + "' and Years<='" + dLastEndYears.ToString("yyyyMM") + "'") + ") as DJ1," +
                    "(select BaseChange from tb_ChangeRateInfo where Area_ID='" + areaList[i][0] + "' and Years=substring('" + dLastStartYears.ToString("yyyyMM") + "',1,4)) as DJ2," +
                    "(select " + listIndicator[2].Replace("sWhere", " and Area_ID='" + areaList[i][0] + "' and Years>='" + dLastStartYears.ToString("yyyyMM") + "' and Years<='" + dLastEndYears.ToString("yyyyMM") + "'") + ") as DY1," +
                    "(select TrafficChange from tb_ChangeRateInfo where Area_ID='" + areaList[i][0] + "' and Years=substring('" + dLastStartYears.ToString("yyyyMM") + "',1,4)) as DY2";
                tmpDvLast = SQLHelper.ExecuteDataView(sqlIndiatos);
                if (tmpDv.Count == 1 && tmpDvLast.Count == 1)
                {
                    Double dx1Day = Double.Parse(tmpDv[0][2].ToString() == "" ? "0.00" : tmpDv[0][2].ToString());
                    Double dx1Last = Double.Parse(tmpDvLast[0][2].ToString() == "" ? "0.00" : tmpDvLast[0][2].ToString());
                    Double dx1 = 0.00;
                    if (dx1Day != 0 && dx1Last != 0)
                    {
                        dx1 = dx1Day / dx1Last - 1;
                    }
                    Double dj1Day = Double.Parse(tmpDv[0][4].ToString() == "" ? "0.00" : tmpDv[0][4].ToString());
                    Double dj1Last = Double.Parse(tmpDvLast[0][4].ToString() == "" ? "0.00" : tmpDvLast[0][4].ToString());
                    Double dj1 = 0.00;
                    if (dj1Day != 0 && dj1Last != 0)
                    {
                        dj1 = dj1Day / dj1Last - 1;
                    }
                    Double dy1Day = Double.Parse(tmpDv[0][6].ToString() == "" ? "0.00" : tmpDv[0][6].ToString());
                    Double dy1Last = Double.Parse(tmpDvLast[0][6].ToString() == "" ? "0.00" : tmpDvLast[0][6].ToString());
                    Double dy1 = 0.00;
                    if (dy1Day != 0 && dy1Last != 0)
                    {
                        dy1 = dy1Day / dy1Last - 1;
                    }
                    DataRow dr = dt.NewRow();
                    string tmpState = String.Empty;
                    dr["ID"] = i;
                    dr["Years"] = sStartYear + "至" + sEndYear;
                    dr["AreaName"] = areaList[i]["Name"];
                    dr["DX1"] = dx1.ToString("0.00");
                    dr["DX2"] = tmpDv[0][3].ToString();
                    if (dx1 > double.Parse(tmpDv[0][3].ToString() == "" ? "0.00" : tmpDv[0][3].ToString()))
                        tmpState = "上升";
                    else if (dx1 < double.Parse(tmpDv[0][3].ToString() == "" ? "0.00" : tmpDv[0][3].ToString()))
                        tmpState = "下降";
                    else
                        tmpState = "持平";
                    dr["DX3"] = tmpState;
                    dr["DJ1"] = dj1.ToString("0.00");
                    dr["DJ2"] = tmpDv[0][5].ToString();
                    if (dj1 > double.Parse(tmpDv[0][5].ToString() == "" ? "0.00" : tmpDv[0][5].ToString()))
                        tmpState = "上升";
                    else if (dj1 < double.Parse(tmpDv[0][5].ToString() == "" ? "0.00" : tmpDv[0][5].ToString()))
                        tmpState = "下降";
                    else
                        tmpState = "持平";
                    dr["DJ3"] = tmpState;
                    dr["DY1"] = dy1.ToString("0.00");
                    dr["DY2"] = tmpDv[0][7].ToString();
                    if (dy1 > double.Parse(tmpDv[0][7].ToString() == "" ? "0.00" : tmpDv[0][7].ToString()))
                        tmpState = "上升";
                    else if (dy1 < double.Parse(tmpDv[0][7].ToString() == "" ? "0.00" : tmpDv[0][7].ToString()))
                        tmpState = "下降";
                    else
                        tmpState = "持平";
                    dr["DY3"] = tmpState;
                    dt.Rows.Add(dr);
                }
            }
            sqlIndiatos = "select '" + 200 + "'as ID," +
                    "'" + sStartYear + "至" + sEndYear + "' as Years," +
                    "(select " + listIndicator[0].Replace("sWhere", " and Years>='" + sStartYear + "' and Years<='" + sEndYear + "'") + ") as DX1," +
                    "(select EnergyChange from tb_ChangeRateInfo where Area_ID='10' and Years=substring('" + sStartYear + "',1,4)) as DX2," +
                    "(select " + listIndicator[1].Replace("sWhere", " and Years>='" + sStartYear + "' and Years<='" + sEndYear + "'") + ") as DJ1," +
                    "(select BaseChange from tb_ChangeRateInfo where Area_ID='10' and Years=substring('" + sStartYear + "',1,4)) as DJ2," +
                    "(select " + listIndicator[2].Replace("sWhere", " and Years>='" + sStartYear + "' and Years<='" + sEndYear + "'") + ") as DY1," +
                    "(select TrafficChange from tb_ChangeRateInfo where Area_ID='10' and Years=substring('" + sStartYear + "',1,4)) as DY2";
            tmpDv = SQLHelper.ExecuteDataView(sqlIndiatos);
            sqlIndiatos = "select '" + 200 + "'as ID," +
                "'" + sStartYear + "至" + sEndYear + "'," +
                "(select " + listIndicator[0].Replace("sWhere", "and Years>='" + dLastStartYears.ToString("yyyyMM") + "' and Years<='" + dLastEndYears.ToString("yyyyMM") + "'") + ") as DX1," +
                "(select EnergyChange from tb_ChangeRateInfo where Area_ID='10' and Years=substring('" + dLastStartYears.ToString("yyyyMM") + "',1,4)) as DX2," +
                "(select " + listIndicator[1].Replace("sWhere", " and Years>='" + dLastStartYears.ToString("yyyyMM") + "' and Years<='" + dLastEndYears.ToString("yyyyMM") + "'") + ") as DJ1," +
                "(select BaseChange from tb_ChangeRateInfo where Area_ID='10' and Years=substring('" + dLastStartYears.ToString("yyyyMM") + "',1,4)) as DJ2," +
                "(select " + listIndicator[2].Replace("sWhere", " and Years>='" + dLastStartYears.ToString("yyyyMM") + "' and Years<='" + dLastEndYears.ToString("yyyyMM") + "'") + ") as DY1," +
                "(select TrafficChange from tb_ChangeRateInfo where Area_ID='10' and Years=substring('" + dLastStartYears.ToString("yyyyMM") + "',1,4)) as DY2";
            tmpDvLast = SQLHelper.ExecuteDataView(sqlIndiatos);
            if (tmpDv.Count == 1 && tmpDvLast.Count == 1)
            {
                Double dx1Day = Double.Parse(tmpDv[0][2].ToString() == "" ? "0.00" : tmpDv[0][2].ToString());
                Double dx1Last = Double.Parse(tmpDvLast[0][2].ToString() == "" ? "0.00" : tmpDvLast[0][2].ToString());
                Double dx1 = 0.00;
                if (dx1Day != 0 && dx1Last != 0)
                {
                    dx1 = dx1Day / dx1Last - 1;
                }
                Double dj1Day = Double.Parse(tmpDv[0][4].ToString() == "" ? "0.00" : tmpDv[0][4].ToString());
                Double dj1Last = Double.Parse(tmpDvLast[0][4].ToString() == "" ? "0.00" : tmpDvLast[0][4].ToString());
                Double dj1 = 0.00;
                if (dj1Day != 0 && dj1Last != 0)
                {
                    dj1 = dj1Day / dj1Last - 1;
                }
                Double dy1Day = Double.Parse(tmpDv[0][6].ToString() == "" ? "0.00" : tmpDv[0][6].ToString());
                Double dy1Last = Double.Parse(tmpDvLast[0][6].ToString() == "" ? "0.00" : tmpDvLast[0][6].ToString());
                Double dy1 = 0.00;
                if (dy1Day != 0 && dy1Last != 0)
                {
                    dy1 = dy1Day / dy1Last - 1;
                }
                DataRow dr = dt.NewRow();
                string tmpState = String.Empty;
                dr["ID"] = 200;
                dr["Years"] = sStartYear + "至" + sEndYear;
                dr["AreaName"] = "安徽省";
                dr["DX1"] = dx1.ToString("0.00");
                dr["DX2"] = tmpDv[0][3].ToString();
                if (dx1 > double.Parse(tmpDv[0][3].ToString() == "" ? "0.00" : tmpDv[0][3].ToString()))
                    tmpState = "上升";
                else if (dx1 < double.Parse(tmpDv[0][3].ToString() == "" ? "0.00" : tmpDv[0][3].ToString()))
                    tmpState = "下降";
                else
                    tmpState = "持平";
                dr["DX3"] = tmpState;
                dr["DJ1"] = dj1.ToString("0.00");
                dr["DJ2"] = tmpDv[0][5].ToString();
                if (dj1 > double.Parse(tmpDv[0][5].ToString() == "" ? "0.00" : tmpDv[0][5].ToString()))
                    tmpState = "上升";
                else if (dj1 < double.Parse(tmpDv[0][5].ToString() == "" ? "0.00" : tmpDv[0][5].ToString()))
                    tmpState = "下降";
                else
                    tmpState = "持平";
                dr["DJ3"] = tmpState;
                dr["DY1"] = dy1.ToString("0.00");
                dr["DY2"] = tmpDv[0][7].ToString();
                if (dy1 > double.Parse(tmpDv[0][7].ToString() == "" ? "0.00" : tmpDv[0][7].ToString()))
                    tmpState = "上升";
                else if (dy1 < double.Parse(tmpDv[0][7].ToString() == "" ? "0.00" : tmpDv[0][7].ToString()))
                    tmpState = "下降";
                else
                    tmpState = "持平";
                dr["DY3"] = tmpState;
                dt.Rows.Add(dr);
            }
            return dt.DefaultView;
        }

        /// <summary>
        /// 生成指标列表 包括分数
        /// </summary>
        /// <param name="sStartYear">起始年月</param>
        /// <param name="sEndYear">结束年月</param>
        /// <returns></returns>
        public DataView GetIndicatorListNew(string sStartYear, string sEndYear, string Area_ID)
        {
            //获取所有的指标项
            DataTable dt = new DataTable();
            dt.Columns.Add("编号", typeof(string));
            dt.Columns.Add("年月", typeof(string));
            dt.Columns.Add("地区", typeof(string));
            DataView dvFormula = SQLHelper.ExecuteDataView("select * from tb_IndexFormula where FormulaParent=3 and YearState=1 order by IsProcedure");
            for (int i = 0; i < dvFormula.Count; i++)
            {
                dt.Columns.Add(dvFormula[i]["IndexName"].ToString(), typeof(string));
                dt.Columns.Add(dvFormula[i]["IndexName"].ToString() + "得分", typeof(string));
            }
            DataRow dr = dt.NewRow();
            dr["编号"] = 1;
            dr["年月"] = sStartYear + "至" + sEndYear;
            dr["地区"] = SQLHelper.ExecuteString("select Name from Area where ID='" + Area_ID + "'");
            string mysql = String.Empty;
            string sStartLastYear = DateTime.Parse(sStartYear.Substring(0, 4) + "-" + sStartYear.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");
            string sEndLastYear = DateTime.Parse(sEndYear.Substring(0, 4) + "-" + sEndYear.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");
            for (int i = 0; i < dvFormula.Count; i++)
            {
                if (dvFormula[i]["YearState"].ToString() == "1")
                {
                    mysql = dvFormula[i]["IndexSQL"].ToString();
                    mysql = mysql.Replace("sWhere", " and Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='" + Area_ID + "'");
                    string tmp = SQLHelper.ExecuteString(mysql);
                    double dYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                    mysql = dvFormula[i]["IndexSQL"].ToString();
                    if (Area_ID == "41")
                    {
                        if (mysql.IndexOf("[tb_ReportAnalysis]") > 0)
                        {
                            mysql = mysql.Replace("sWhere", " and Years >='" + sStartLastYear + "' and Years<='" + sEndLastYear + "'");
                        }
                        else
                        {
                            mysql = mysql.Replace("sWhere", " and Years >='" + sStartLastYear + "' and Years<='" + sEndLastYear + "' and Area_ID='" + Area_ID + "'");
                        }
                    }
                    else
                    {
                        mysql = mysql.Replace("sWhere", " and Years >='" + sStartLastYear + "' and Years<='" + sEndLastYear + "' and Area_ID='" + Area_ID + "'");
                    }
                    tmp = SQLHelper.ExecuteString(mysql);
                    double dLastYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                    double dRate = 0.00;
                    if (dLastYearData == 0)
                    {
                        dRate = 0.00;
                        dr[dvFormula[i]["IndexName"].ToString()] = 0;
                    }
                    else
                    {
                        dRate = dYearData / dLastYearData - 1;
                        dr[dvFormula[i]["IndexName"].ToString()] = dRate.ToString("0.00");
                    }
                    double fs = double.Parse(dvFormula[i]["OutOf"].ToString());
                    string sGoal = SQLHelper.ExecuteString("select top 1 " + dvFormula[i]["FormulaNo"].ToString() + " from tb_ChangeRateInfo where Area_ID='41' and Years='" + sStartYear + "'");
                    double dGoal = double.Parse(String.IsNullOrEmpty(sGoal) ? "0.00" : sGoal);
                    //计算得分
                    if (dRate >= dGoal * 0.6 && dGoal != 0)
                    {
                        dr[dvFormula[i]["IndexName"].ToString() + "得分"] = dRate * fs / dGoal;
                    }
                    else
                    {
                        dr[dvFormula[i]["IndexName"].ToString() + "得分"] = "0";
                    }
                }
            }
            dt.Rows.Add(dr);
            return dt.DefaultView;
        }

        /// <summary>
        /// 生成存储过程
        /// </summary>
        /// <param name="sqlFormula">公式</param>
        /// <param name="sqlName">存储过程名称</param>
        public string SaveFormula(string sqlFormula, string sqlName)
        {
            //最后生成的SQL语句
            string sFormulaSQL = String.Empty;
            //临时变量 保存原始SQL语句
            string tmpFormula = sqlFormula;
            string tmpSQL = String.Empty;
            string tmpData = String.Empty;
            string[] aryList = { null, null };
            //定义三种获取公式中 SUM AVG 函数的方法，同时分析字段项
            string regexSumStr = @"sum\(\w*!\w*\)";
            string regexAvgStr = @"avg\(\w*!\w*\)";
            string regexSigStr = @"sig\(\w*!\w*\)";
            string regexFiled = @"\w*!\w*";
            Regex rSum = new Regex(regexSumStr, RegexOptions.None);
            Regex rAvg = new Regex(regexAvgStr, RegexOptions.None);
            Regex rSig = new Regex(regexSigStr, RegexOptions.None);
            Regex rField = new Regex(regexFiled, RegexOptions.None);
            Match mFiled = null;
            //获取所有的SUM函数匹配项
            MatchCollection mcs = rSum.Matches(tmpFormula);
            int i = 0;
            foreach (Match mc in mcs)
            {
                i++;
                mFiled = rField.Match(mc.ToString());
                //分隔字段和表名
                aryList = mFiled.ToString().Split('!');
                tmpSQL += "declare @" + sqlName + "a" + i + " decimal(18,2);";
                tmpSQL += "select @" + sqlName + "a" + i + "=sum([" + aryList[1] + "]) from [" + aryList[0] + "] where (1=1) sWhere;";
                tmpFormula = tmpFormula.Replace(mc.ToString(), "@" + sqlName + "a" + i);
            }
            i = 0;
            mcs = rAvg.Matches(tmpFormula);
            foreach (Match mc in mcs)
            {
                i++;
                mFiled = rField.Match(mc.ToString());
                aryList = mFiled.ToString().Split('!');
                tmpSQL += "declare @" + sqlName + "b" + i + " decimal(18,2);";
                tmpSQL += "select @" + sqlName + "b" + i + " =avg([" + aryList[1] + "]) from [" + aryList[0] + "] where (1=1) sWhere;";
                tmpFormula = tmpFormula.Replace(mc.ToString(), "@" + sqlName + "b" + i);
            }
            i = 0;
            mcs = rSig.Matches(tmpFormula);
            foreach (Match mc in mcs)
            {
                i++;
                mFiled = rField.Match(mc.ToString());
                aryList = mFiled.ToString().Split('!');
                tmpSQL += "declare @" + sqlName + "b" + i + " decimal(18,2);";
                tmpSQL += "select @" + sqlName + "b" + i + " =sum([" + aryList[1] + "]) from [" + aryList[0] + "] where (1=1) sSigWhere;";
                tmpFormula = tmpFormula.Replace(mc.ToString(), "@" + sqlName + "b" + i);
            }
            i = 0;
            mcs = rField.Matches(tmpFormula);
            foreach (Match mc in mcs)
            {
                i++;
                aryList = mc.ToString().Split('!');
                tmpSQL += "declare @" + sqlName + "c" + i + " decimal(18,2);";
                tmpSQL += "select @" + sqlName + "c" + i + "=sum([" + aryList[1] + "]) from [" + aryList[0] + "] where (1=1) sWhere;";
                tmpFormula = tmpFormula.Replace(mc.ToString(), "@" + sqlName + "c" + i);
            }
            return tmpSQL + "select cast(" + tmpFormula + " as varchar(200)) as " + sqlName;
        }

        /// <summary>
        /// 混合查询 
        /// </summary>
        /// <param name="sArea">地区</param>
        /// <param name="sStartYears">起始年月</param>
        /// <param name="sEndYears">终止年月</param>
        /// <param name="sField">查询字段</param>
        /// <param name="sFormula">查询公式</param>
        /// <param name="sIndi">查询指标</param>
        /// <param name="sConfig">配置信息 同比 环比 默认</param>
        /// <param name="slj">是否累计</param>
        /// <returns></returns>
        public DataView QueryTableField(string sArea, string sStartYears, string sEndYears, string sField, string sFormula, string sIndi, string sConfig, bool bTotal)
        {
            DataView dvFormula = new DataView();
            DataView dvIndi = new DataView();
            Dictionary<string, DataView> listDv = new Dictionary<string, DataView>();
            if (!String.IsNullOrEmpty(sField))
            {
                string[] ary = sField.Split(',');
                for (int i = 0; i < ary.Length; i++)
                {
                    string[] arylist = ary[i].Split('^');
                    string[] aryField = arylist[1].Split('|');
                    listDv.Add(arylist[0], this.QueryTableField(sArea, sStartYears, sEndYears, arylist[0] + "^" + aryField[1], "|" + aryField[0].Replace("rt_", ""), sConfig, bTotal));
                }
            }
            if (!String.IsNullOrEmpty(sFormula))
            {
                dvFormula = this.QueryFormula(sArea, sStartYears, sEndYears, sFormula, "", sConfig, bTotal);
            }
            if (!String.IsNullOrEmpty(sIndi))
            {
                //string YearsLast = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");
                //dvIndi = this.QueryQuantity(sArea, sStartYears, sEndYears, sIndi, "", sConfig, bTotal, YearsLast);
                string[] aryList = sIndi.Split(',');
                List<string> field = new List<string>();
                for (int i = 0; i < aryList.Length; i++)
                {
                    string[] ary = aryList[i].Split('^');
                    string tmp = SQLHelper.ExecuteString("select IF_ID from tb_IndexFormula where IndexName='" + ary[0] + "'");
                    field.Add(tmp);
                }
                string[] aryarea = sArea.Split(',');
                List<string> area = new List<string>();
                for (int i = 0; i < aryarea.Length; i++)
                {
                    string[] ary = aryarea[i].Split('^');
                    area.Add(ary[0]);
                }
                string sAreaID = String.Empty;
                string sIndiID = String.Empty;
                foreach (string indi in field)
                {
                    sIndiID += String.IsNullOrEmpty(sIndiID) ? indi : "," + indi;
                }
                foreach (string id in area)
                {
                    sAreaID += String.IsNullOrEmpty(sAreaID) ? id : "," + id;
                }
                dvIndi = this.GetIndicatorListNewArea(sStartYears, sEndYears, sAreaID, sIndiID, "2");
            }
            DataTable dt = new DataTable("dt_Query");
            dt.Columns.Add(new DataColumn("年月", typeof(string)));
            dt.Columns.Add(new DataColumn("地区", typeof(string)));
            string[] sAry = sField.Split(',');
            List<string> listField = new List<string>();
            if (!String.IsNullOrEmpty(sField))
            {
                for (int i = 0; i < sAry.Length; i++)
                {
                    string[] tmp = sAry[i].Split('^');
                    dt.Columns.Add(new DataColumn(tmp[0], typeof(string)));
                    listField.Add(tmp[0]);
                }
            }
            sAry = sFormula.Split(',');
            List<string> listFormula = new List<string>();
            if (!String.IsNullOrEmpty(sFormula))
            {
                for (int i = 0; i < sAry.Length; i++)
                {
                    string[] tmp = sAry[i].Split('^');
                    dt.Columns.Add(new DataColumn(tmp[0], typeof(string)));
                    listFormula.Add(tmp[0]);
                }
            }
            sAry = sIndi.Split(',');
            List<string> listIndi = new List<string>();
            if (!String.IsNullOrEmpty(sIndi))
            {
                for (int i = 0; i < sAry.Length; i++)
                {
                    string[] tmp = sAry[i].Split('^');
                    dt.Columns.Add(new DataColumn(tmp[0], typeof(string)));
                    listIndi.Add(tmp[0]);
                }
            }
            if (!String.IsNullOrEmpty(sIndi))
            {
                for (int i = 0; i < dvIndi.Count; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["年月"] = dvIndi[i]["年月"];
                    dr["地区"] = dvIndi[i]["地区"];
                    foreach (string tmp in listIndi)
                    {
                        dr[tmp] = dvIndi[i][tmp];
                    }
                    if (!String.IsNullOrEmpty(sFormula))
                    {
                        foreach (string tmp in listFormula)
                        {
                            dr[tmp] = dvFormula[i][tmp];
                        }
                    }
                    if (!String.IsNullOrEmpty(sField))
                    {
                        foreach (string tmp in listField)
                        {
                            dr[tmp] = String.Empty;
                            DataView dvTemp = listDv[tmp];
                            for (int j = 0; j < dvTemp.Count; j++)
                            {
                                if (dvIndi[i]["年月"].ToString().IndexOf(dvTemp[j]["年月"].ToString()) >= 0 && dvTemp[j]["地区"].ToString() == dvIndi[i]["地区"].ToString())
                                {
                                    dr[tmp] = dvTemp[j][tmp];
                                    break;
                                }
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                }
                return dt.DefaultView;
            }
            if (!String.IsNullOrEmpty(sFormula))
            {
                for (int i = 0; i < dvFormula.Count; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["年月"] = dvFormula[i]["年月"];
                    dr["地区"] = dvFormula[i]["地区"];
                    if (!String.IsNullOrEmpty(sIndi))
                    {
                        foreach (string tmp in listIndi)
                        {
                            dr[tmp] = dvIndi[i][tmp];
                        }
                    }
                    foreach (string tmp in listFormula)
                    {
                        dr[tmp] = dvFormula[i][tmp];
                    }

                    if (!String.IsNullOrEmpty(sField))
                    {
                        foreach (string tmp in listField)
                        {
                            dr[tmp] = String.Empty;
                            DataView dvTemp = listDv[tmp];
                            for (int j = 0; j < dvTemp.Count; j++)
                            {
                                if (dvTemp[j]["年月"].ToString() == dvFormula[i]["年月"].ToString() && dvTemp[j]["地区"].ToString() == dvFormula[i]["地区"].ToString())
                                {
                                    dr[tmp] = dvTemp[j][tmp];
                                    break;
                                }
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                }
                return dt.DefaultView;
            }
            if (!String.IsNullOrEmpty(sField) && !String.IsNullOrEmpty(sFormula))
            {
                for (int i = 0; i < dvFormula.Count; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["年月"] = dvFormula[i]["年月"];
                    dr["地区"] = dvFormula[i]["地区"];
                    if (!String.IsNullOrEmpty(sIndi))
                    {
                        foreach (string tmp in listIndi)
                        {
                            dr[tmp] = dvIndi[i][tmp];
                        }
                    }
                    if (!String.IsNullOrEmpty(sFormula))
                    {
                        foreach (string tmp in listFormula)
                        {
                            dr[tmp] = dvFormula[i][tmp];
                        }
                    }
                    foreach (string tmp in listField)
                    {
                        dr[tmp] = String.Empty;
                        DataView dvTemp = listDv[tmp];
                        for (int j = 0; j < dvTemp.Count; j++)
                        {
                            if (dvTemp[j]["年月"].ToString() == dvIndi[i]["年月"].ToString() && dvTemp[j]["地区"].ToString() == dvIndi[i]["地区"].ToString())
                            {
                                dr[tmp] = dvTemp[j][tmp];
                                break;
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                }
                return dt.DefaultView;
            }
            if (!String.IsNullOrEmpty(sField) && String.IsNullOrEmpty(sFormula))
            {
                int max = 0;
                string maxtmp = String.Empty;
                foreach (string tmp in listDv.Keys)
                {
                    if (listDv[tmp].Count > max)
                    {
                        max = listDv[tmp].Count;
                        maxtmp = tmp;
                    }
                }
                for (int i = 0; i < max; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["年月"] = listDv[maxtmp][i]["年月"];
                    dr["地区"] = listDv[maxtmp][i]["地区"];
                    foreach (string tmp in listField)
                    {
                        dr[tmp] = String.Empty;
                        DataView dvTemp = listDv[tmp];
                        for (int j = 0; j < dvTemp.Count; j++)
                        {
                            if (dvTemp[j]["年月"].ToString() == listDv[maxtmp][i]["年月"].ToString() && dvTemp[j]["地区"].ToString() == listDv[maxtmp][i]["地区"].ToString())
                            {
                                dr[tmp] = dvTemp[j][tmp];
                                break;
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                }
                return dt.DefaultView;
            }
            return dt.DefaultView;
        }

        public DataView GetIndicatorListNewArea(string sStartYear, string sEndYear, string Area_ID, string sIndiList, string sType)
        {
            Dictionary<string, string> listField = new Dictionary<string, string>();
            if (Area_ID == "")
            {
                #region 查询全省指标信息
                DataTable dt = new DataTable();
                dt.Columns.Add("编号", typeof(string));
                dt.Columns.Add("年月", typeof(string));
                dt.Columns.Add("地区", typeof(string));
                string swhere = String.Empty;
                if (sIndiList != "")
                {
                    swhere += " and IF_ID in (" + sIndiList + ")";
                }
                else
                {
                    swhere += "";
                }
                DataView dvFormula = SQLHelper.ExecuteDataView("select * from tb_IndexFormula where FormulaParent=3 and (YearState=1 or YearState=2 or YearState=3) " + swhere + " order by IsProcedure");
                for (int i = 0; i < dvFormula.Count; i++)
                {
                    dt.Columns.Add(dvFormula[i]["IndexName"].ToString(), typeof(string));
                    dt.Columns.Add(dvFormula[i]["IndexName"].ToString() + "目标", typeof(string));
                    dt.Columns.Add(dvFormula[i]["IndexName"].ToString() + "得分", typeof(string));
                    dt.Columns.Add(dvFormula[i]["IndexName"].ToString() + "状态", typeof(string));
                    if (dvFormula[i]["YearState"].ToString() == "2")
                    {
                        listField.Add(dvFormula[i]["IndexName"].ToString(), dvFormula[i]["OutOf"].ToString());
                    }
                }
                DataView areaList = SQLHelper.ExecuteDataView("select [ID],[Name],[ParentID] from Area where ParentID=10 and ID!='41' order by ID");
                for (int j = 0; j < areaList.Count; j++)
                {
                    Area_ID = areaList[j]["ID"].ToString();
                    if (Area_ID == "39")
                        continue;
                    //获取所有的指标项
                    DataRow dr = dt.NewRow();
                    dr["编号"] = j + 1;
                    dr["年月"] = sStartYear + "至" + sEndYear;
                    dr["地区"] = areaList[j]["Name"].ToString();
                    string mysql = String.Empty;
                    string sStartLastYear = DateTime.Parse(sStartYear.Substring(0, 4) + "-" + sStartYear.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");
                    string sEndLastYear = DateTime.Parse(sEndYear.Substring(0, 4) + "-" + sEndYear.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");
                    for (int i = 0; i < dvFormula.Count; i++)
                    {

                        mysql = dvFormula[i]["IndexSQL"].ToString();
                        mysql = mysql.Replace("sWhere", " and Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='" + Area_ID + "'");
                        mysql = mysql.Replace("sSigWhere", " and Years ='" + sEndYear + "' and Area_ID='" + Area_ID + "'");
                        string tmp = SQLHelper.ExecuteString(mysql);
                        double dYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                        string tmpType = dvFormula[i]["YearState"].ToString();
                        if (tmpType == "1")
                        {
                            mysql = dvFormula[i]["IndexSQL"].ToString();
                            mysql = mysql.Replace("sWhere", " and Years >='" + sStartLastYear + "' and Years<='" + sEndLastYear + "' and Area_ID='" + Area_ID + "'");
                            mysql = mysql.Replace("sSigWhere", " and Years ='" + sEndYear + "' and Area_ID='" + Area_ID + "'");
                            tmp = SQLHelper.ExecuteString(mysql);
                            double dLastYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                            double dRate = 0.00;
                            if (dLastYearData == 0)
                            {
                                dRate = 0.00;
                                dr[dvFormula[i]["IndexName"].ToString()] = 0;
                            }
                            else
                            {
                                dRate = dYearData / dLastYearData - 1;
                                dr[dvFormula[i]["IndexName"].ToString()] = (dRate * 100).ToString("0.00") + "%";
                            }
                            string eValue = String.Empty;
                            if (dvFormula[i]["IndexName"].ToString().IndexOf("综合能耗") > 0)
                            {
                                eValue = SQLHelper.ExecuteString("select -EnergyChange*100 from tb_ChangeRateInfo where Years='" + sStartYear.Substring(0, 4) + "' and Area_ID='" + Area_ID + "'");
                                dr[dvFormula[i]["IndexName"].ToString() + "目标"] = eValue + "%";
                            }
                            else if (dvFormula[i]["IndexName"].ToString().IndexOf("载频") > 0)
                            {
                                eValue = SQLHelper.ExecuteString("select -BaseChange*100 from tb_ChangeRateInfo where Years='" + sStartYear.Substring(0, 4) + "' and Area_ID='" + Area_ID + "'");
                                dr[dvFormula[i]["IndexName"].ToString() + "目标"] = eValue + "%";
                            }
                            else
                            {
                                eValue = SQLHelper.ExecuteString("select -TrafficChange*100 from tb_ChangeRateInfo where Years='" + sStartYear.Substring(0, 4) + "' and Area_ID='" + Area_ID + "'");
                                dr[dvFormula[i]["IndexName"].ToString() + "目标"] = eValue + "%";
                            }
                            double fs = double.Parse(dvFormula[i]["OutOf"].ToString());
                            string sGoal = SQLHelper.ExecuteString("select top 1 " + dvFormula[i]["FormulaNo"].ToString() + " from tb_ChangeRateInfo where Area_ID='" + Area_ID + "' and Years='" + sStartYear.Substring(0, 4) + "' and Area_ID='" + Area_ID + "'");
                            double dGoal = double.Parse(String.IsNullOrEmpty(sGoal) ? "0.00" : sGoal);
                            dr[dvFormula[i]["IndexName"].ToString() + "状态"] = dRate * 100 <= double.Parse(eValue) ? "达标" : "不达标";
                            //计算得分 
                            if (dRate >= -dGoal * 0.6 && dGoal != 0)
                            {
                                dr[dvFormula[i]["IndexName"].ToString() + "得分"] = fs;
                            }
                            else
                            {
                                if (dRate <= -dGoal * 0.6 && dGoal != 0)
                                {
                                    double df = ((dRate + dGoal * 0.6) * fs / -(dGoal - dGoal * 0.6));
                                    if (df > fs)
                                        df = fs;
                                    dr[dvFormula[i]["IndexName"].ToString() + "得分"] = df.ToString("0.0");
                                }
                                else
                                {
                                    dr[dvFormula[i]["IndexName"].ToString() + "得分"] = "0";
                                }
                            }
                        }
                        else if (tmpType == "2")
                        {
                            dr[dvFormula[i]["IndexName"].ToString()] = dYearData.ToString("0.00");
                            dr[dvFormula[i]["IndexName"].ToString() + "目标"] = "";
                        }
                        else if (tmpType == "3")
                        {
                            mysql = dvFormula[i]["IndexSQL"].ToString();
                            mysql = mysql.Replace("sWhere", " and Years >='" + sStartLastYear + "' and Years<='" + sEndLastYear + "' and Area_ID='" + Area_ID + "'");
                            tmp = SQLHelper.ExecuteString(mysql);
                            double dLastYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                            string sCommpanyTrffic = SQLHelper.ExecuteString("select sum(cast(Value as decimal(18,2))) from rt_Traffic where Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='" + Area_ID + "'");
                            double dCommpanyTrffic = Double.Parse(String.IsNullOrEmpty(sCommpanyTrffic) ? "0.00" : sCommpanyTrffic);
                            //各公司节电量
                            double dCommpanyEnery = (dYearData - dLastYearData) * dCommpanyTrffic;

                            mysql = dvFormula[i]["IndexSQL"].ToString();
                            mysql = mysql.Replace("sWhere", " and Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='41'");
                            tmp = SQLHelper.ExecuteString(mysql);
                            double dSGSYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                            mysql = dvFormula[i]["IndexSQL"].ToString();
                            mysql = mysql.Replace("sWhere", " and Years >='" + sStartLastYear + "' and Years<='" + sEndLastYear + "' and Area_ID='41'");
                            tmp = SQLHelper.ExecuteString(mysql);
                            double dSGSLastYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                            string sSGSCommpanyTrffic = SQLHelper.ExecuteString("select sum(cast(Value as decimal(18,2))) from rt_Traffic where Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='41'");
                            double dSGSCommpanyTrffic = Double.Parse(String.IsNullOrEmpty(sSGSCommpanyTrffic) ? "0.00" : sSGSCommpanyTrffic);
                            //省公司节电量
                            double dSGSCommpanyEnery = (dSGSYearData - dSGSLastYearData) * dSGSCommpanyTrffic;
                            //dr[dvFormula[i]["IndexName"].ToString()] = (dSGSCommpanyEnery / dCommpanyEnery).ToString("0.00");
                            dr[dvFormula[i]["IndexName"].ToString()] = dCommpanyEnery.ToString("0.00");
                            dr[dvFormula[i]["IndexName"].ToString() + "目标"] = "";

                            //耗电量占比
                            string sSGSEnery = SQLHelper.ExecuteString("select sum(Power*0.1229+Gasoline*1.4714*0.72+DieselFuel*1.4571*0.83) from rt_EnergyConsumption where Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='41'");
                            string sEnery = SQLHelper.ExecuteString("select sum(Power*0.1229+Gasoline*1.4714*0.72+DieselFuel*1.4571*0.83) from rt_EnergyConsumption where Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='" + Area_ID + "'");
                            double dCommpany = Double.Parse(String.IsNullOrEmpty(sSGSEnery) ? "0.00" : sSGSEnery) / Double.Parse(String.IsNullOrEmpty(sEnery) ? "0.00" : sEnery);
                            dr[dvFormula[i]["IndexName"].ToString() + "得分"] = (2.5 + ((dSGSCommpanyEnery / dCommpanyEnery) - dCommpany) / 100 * 17).ToString("0.0");
                        }
                    }
                    dt.Rows.Add(dr);
                }
                //计算比高法得分
                foreach (string sIndex in listField.Keys)
                {
                    double fs = Double.Parse(listField[sIndex]);
                    double max = 0.00;
                    double min = 0.00;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        double tmp = Double.Parse(String.IsNullOrEmpty(dt.Rows[i][sIndex].ToString()) ? "0.00" : dt.Rows[i][sIndex].ToString());
                        if (tmp > max)
                            max = tmp;
                        if (tmp < min)
                            min = tmp;
                    }
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        double tmp = Double.Parse(String.IsNullOrEmpty(dt.Rows[i][sIndex].ToString()) ? "0.00" : dt.Rows[i][sIndex].ToString());
                        if (tmp == max)
                        {
                            dt.Rows[i][sIndex + "得分"] = fs;
                        }
                        else if (tmp == min)
                        {
                            dt.Rows[i][sIndex + "得分"] = "0";
                        }
                        else
                        {
                            dt.Rows[i][sIndex + "得分"] = (tmp * fs / max).ToString("0.0");
                        }
                    }
                }
                List<string> listCol = new List<string>();
                for (int i = 3; i < dt.Columns.Count; i++)
                {
                    if (sType == "1")
                    {
                        if (dt.Columns[i].ColumnName.IndexOf("得分") < 0)
                        {
                            listCol.Add(dt.Columns[i].ColumnName);
                        }
                    }
                    else
                    {
                        if (dt.Columns[i].ColumnName.IndexOf("得分") >= 0 ||
                            (dt.Columns[i].ColumnName.IndexOf("节能") >= 0 && dt.Columns[i].ColumnName.IndexOf("目标") >= 0) ||
                            (dt.Columns[i].ColumnName.IndexOf("节能") >= 0 && dt.Columns[i].ColumnName.IndexOf("状态") >= 0) ||
                            (dt.Columns[i].ColumnName.IndexOf("使用") >= 0 && dt.Columns[i].ColumnName.IndexOf("目标") >= 0) ||
                            (dt.Columns[i].ColumnName.IndexOf("使用") >= 0 && dt.Columns[i].ColumnName.IndexOf("状态") >= 0))
                        {
                            listCol.Add(dt.Columns[i].ColumnName);
                        }
                    }
                }
                foreach (string tmp in listCol)
                {
                    dt.Columns.Remove(tmp);
                }
                return dt.DefaultView;
                #endregion
            }
            else
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("编号", typeof(string));
                dt.Columns.Add("年月", typeof(string));
                dt.Columns.Add("地区", typeof(string));
                string swhere = String.Empty;
                if (Area_ID == "41")
                    swhere = "and (YearState=1) ";
                else
                    swhere = "and (YearState=1 or YearState=2 or YearState=3) ";
                if (sIndiList != "")
                {
                    swhere += " and IF_ID in (" + sIndiList + ")";
                }
                else
                {
                    swhere += "";
                }
                DataView dvFormula = SQLHelper.ExecuteDataView("select * from tb_IndexFormula where FormulaParent=3 " + swhere + " order by IsProcedure");
                for (int i = 0; i < dvFormula.Count; i++)
                {
                    dt.Columns.Add(dvFormula[i]["IndexName"].ToString(), typeof(string));
                    dt.Columns.Add(dvFormula[i]["IndexName"].ToString() + "目标", typeof(string));
                    dt.Columns.Add(dvFormula[i]["IndexName"].ToString() + "得分", typeof(string));
                    dt.Columns.Add(dvFormula[i]["IndexName"].ToString() + "状态", typeof(string));
                    if (dvFormula[i]["YearState"].ToString() == "2")
                    {
                        listField.Add(dvFormula[i]["IndexName"].ToString(), dvFormula[i]["OutOf"].ToString());
                    }
                }
                DataView areaList = SQLHelper.ExecuteDataView("select [ID],[Name],[ParentID] from Area where ParentID=10 and ID in (" + Area_ID + ") order by ID");
                for (int j = 0; j < areaList.Count; j++)
                {
                    Area_ID = areaList[j]["ID"].ToString();
                    if (Area_ID == "39")
                        continue;
                    //获取所有的指标项
                    DataRow dr = dt.NewRow();
                    dr["编号"] = j + 1;
                    dr["年月"] = sStartYear + "至" + sEndYear;
                    dr["地区"] = areaList[j]["Name"].ToString();
                    string mysql = String.Empty;
                    string sStartLastYear = DateTime.Parse(sStartYear.Substring(0, 4) + "-" + sStartYear.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");
                    string sEndLastYear = DateTime.Parse(sEndYear.Substring(0, 4) + "-" + sEndYear.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");
                    for (int i = 0; i < dvFormula.Count; i++)
                    {

                        mysql = dvFormula[i]["IndexSQL"].ToString();
                        mysql = mysql.Replace("sWhere", " and Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='" + Area_ID + "'");
                        mysql = mysql.Replace("sSigWhere", " and Years ='" + sEndYear + "' and Area_ID='" + Area_ID + "'");
                        string tmp = SQLHelper.ExecuteString(mysql);
                        double dYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                        string tmpType = dvFormula[i]["YearState"].ToString();
                        if (tmpType == "1")
                        {
                            mysql = dvFormula[i]["IndexSQL"].ToString();
                            mysql = mysql.Replace("sWhere", " and Years >='" + sStartLastYear + "' and Years<='" + sEndLastYear + "' and Area_ID='" + Area_ID + "'");
                            mysql = mysql.Replace("sSigWhere", " and Years ='" + sEndYear + "' and Area_ID='" + Area_ID + "'");
                            tmp = SQLHelper.ExecuteString(mysql);
                            double dLastYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                            double dRate = 0.00;
                            if (dLastYearData == 0)
                            {
                                dRate = 0.00;
                                dr[dvFormula[i]["IndexName"].ToString()] = 0;
                            }
                            else
                            {
                                dRate = dYearData / dLastYearData - 1;
                                dr[dvFormula[i]["IndexName"].ToString()] = (dRate * 100).ToString("0.00") + "%";
                            }
                            string eValue = String.Empty;
                            if (dvFormula[i]["IndexName"].ToString().IndexOf("综合能耗") > 0)
                            {
                                if (Area_ID == "41")
                                    eValue = SQLHelper.ExecuteString("select -fTelecomRate*100 from tb_ChangeRate where Years='" + sStartYear.Substring(0, 4) + "'");
                                else
                                    eValue = SQLHelper.ExecuteString("select -EnergyChange*100 from tb_ChangeRateInfo where Years='" + sStartYear.Substring(0, 4) + "' and Area_ID='" + Area_ID + "'");
                                dr[dvFormula[i]["IndexName"].ToString() + "目标"] = eValue + "%";
                            }
                            else if (dvFormula[i]["IndexName"].ToString().IndexOf("载频") > 0)
                            {
                                if (Area_ID == "41")
                                    eValue = SQLHelper.ExecuteString("select -fBaseRate*100 from tb_ChangeRate where Years='" + sStartYear.Substring(0, 4) + "'");
                                else
                                    eValue = SQLHelper.ExecuteString("select -BaseChange*100 from tb_ChangeRateInfo where Years='" + sStartYear.Substring(0, 4) + "' and Area_ID='" + Area_ID + "'");
                                dr[dvFormula[i]["IndexName"].ToString() + "目标"] = eValue + "%";
                            }
                            else
                            {
                                if (Area_ID == "41")
                                    eValue = SQLHelper.ExecuteString("select -fBusinessRate*100 from tb_ChangeRate where Years='" + sStartYear.Substring(0, 4) + "'");
                                else
                                    eValue = SQLHelper.ExecuteString("select -TrafficChange*100 from tb_ChangeRateInfo where Years='" + sStartYear.Substring(0, 4) + "' and Area_ID='" + Area_ID + "'");
                                dr[dvFormula[i]["IndexName"].ToString() + "目标"] = eValue + "%";
                            }
                            double fs = double.Parse(dvFormula[i]["OutOf"].ToString());
                            string sGoal = String.Empty;
                            if (Area_ID == "41")
                                sGoal = SQLHelper.ExecuteString("select top 1 f" + dvFormula[i]["FormulaNo"].ToString().Replace("Change", "Rate").Replace("Traffic", "Telecom").Replace("Energy", "Business") + " from tb_ChangeRate where Years='" + sStartYear.Substring(0, 4) + "'");
                            else
                                sGoal = SQLHelper.ExecuteString("select top 1 " + dvFormula[i]["FormulaNo"].ToString() + " from tb_ChangeRateInfo where Area_ID='" + Area_ID + "' and Years='" + sStartYear.Substring(0, 4) + "' ");
                            double dGoal = double.Parse(String.IsNullOrEmpty(sGoal) ? "0.00" : sGoal);
                            if (String.IsNullOrEmpty(eValue))
                                eValue = "0.00";
                            dr[dvFormula[i]["IndexName"].ToString() + "状态"] = dRate * 100 <= double.Parse(eValue) ? "达标" : "不达标";
                            //计算得分     
                            if (dRate >= -dGoal * 0.6 && dGoal != 0)
                            {
                                dr[dvFormula[i]["IndexName"].ToString() + "得分"] = fs;
                            }
                            else
                            {
                                if (dRate <= -dGoal * 0.6 && dGoal != 0)
                                {
                                    double df = ((dRate + dGoal * 0.6) * fs / -(dGoal - dGoal * 0.6));
                                    if (df > fs)
                                        df = fs;
                                    dr[dvFormula[i]["IndexName"].ToString() + "得分"] = df.ToString("0.0");
                                }
                                else
                                {
                                    dr[dvFormula[i]["IndexName"].ToString() + "得分"] = "0";
                                }
                            }
                        }
                        else if (tmpType == "2" && Area_ID != "41")
                        {
                            dr[dvFormula[i]["IndexName"].ToString()] = dYearData.ToString("0.00");
                            dr[dvFormula[i]["IndexName"].ToString() + "目标"] = "";
                        }
                        else if (tmpType == "3" && Area_ID != "41")
                        {
                            mysql = dvFormula[i]["IndexSQL"].ToString();
                            mysql = mysql.Replace("sWhere", " and Years >='" + sStartLastYear + "' and Years<='" + sEndLastYear + "' and Area_ID='" + Area_ID + "'");
                            tmp = SQLHelper.ExecuteString(mysql);
                            double dLastYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                            string sCommpanyTrffic = SQLHelper.ExecuteString("select sum(cast(Value as decimal(18,2))) from rt_Traffic where Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='" + Area_ID + "'");
                            double dCommpanyTrffic = Double.Parse(String.IsNullOrEmpty(sCommpanyTrffic) ? "0.00" : sCommpanyTrffic);
                            //各公司节电量
                            double dCommpanyEnery = (dYearData - dLastYearData) * dCommpanyTrffic;

                            mysql = dvFormula[i]["IndexSQL"].ToString();
                            mysql = mysql.Replace("sWhere", " and Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='41'");
                            tmp = SQLHelper.ExecuteString(mysql);
                            double dSGSYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                            mysql = dvFormula[i]["IndexSQL"].ToString();
                            mysql = mysql.Replace("sWhere", " and Years >='" + sStartLastYear + "' and Years<='" + sEndLastYear + "' and Area_ID='41'");
                            tmp = SQLHelper.ExecuteString(mysql);
                            double dSGSLastYearData = Double.Parse(String.IsNullOrEmpty(tmp) ? "0.00" : tmp);
                            string sSGSCommpanyTrffic = SQLHelper.ExecuteString("select sum(cast(Value as decimal(18,2))) from rt_Traffic where Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='41'");
                            double dSGSCommpanyTrffic = Double.Parse(String.IsNullOrEmpty(sSGSCommpanyTrffic) ? "0.00" : sSGSCommpanyTrffic);
                            //省公司节电量
                            double dSGSCommpanyEnery = (dSGSYearData - dSGSLastYearData) * dSGSCommpanyTrffic;
                            //dr[dvFormula[i]["IndexName"].ToString()] = (dSGSCommpanyEnery / dCommpanyEnery).ToString("0.00");
                            dr[dvFormula[i]["IndexName"].ToString()] = dCommpanyEnery.ToString("0.00");
                            dr[dvFormula[i]["IndexName"].ToString() + "目标"] = "";

                            //耗电量占比
                            string sSGSEnery = SQLHelper.ExecuteString("select sum(Power*0.1229+Gasoline*1.4714*0.72+DieselFuel*1.4571*0.83) from rt_EnergyConsumption where Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='41'");
                            string sEnery = SQLHelper.ExecuteString("select sum(Power*0.1229+Gasoline*1.4714*0.72+DieselFuel*1.4571*0.83) from rt_EnergyConsumption where Years >='" + sStartYear + "' and Years<='" + sEndYear + "' and Area_ID='" + Area_ID + "'");
                            double dCommpany = Double.Parse(String.IsNullOrEmpty(sSGSEnery) ? "0.00" : sSGSEnery) / Double.Parse(String.IsNullOrEmpty(sEnery) ? "0.00" : sEnery);
                            dr[dvFormula[i]["IndexName"].ToString() + "得分"] = (2.5 + ((dSGSCommpanyEnery / dCommpanyEnery) - dCommpany) / 100 * 17).ToString("0.0");
                        }
                    }
                    dt.Rows.Add(dr);
                }
                //计算比高法得分
                if (Area_ID != "41")
                {
                    foreach (string sIndex in listField.Keys)
                    {
                        double fs = Double.Parse(listField[sIndex]);
                        double max = 0.00;
                        double min = 0.00;
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            double tmp = Double.Parse(String.IsNullOrEmpty(dt.Rows[i][sIndex].ToString()) ? "0.00" : dt.Rows[i][sIndex].ToString());
                            if (tmp > max)
                                max = tmp;
                            if (tmp < min)
                                min = tmp;
                        }
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            double tmp = Double.Parse(String.IsNullOrEmpty(dt.Rows[i][sIndex].ToString()) ? "0.00" : dt.Rows[i][sIndex].ToString());
                            if (tmp == max)
                            {
                                dt.Rows[i][sIndex + "得分"] = fs;
                            }
                            else if (tmp == min)
                            {
                                dt.Rows[i][sIndex + "得分"] = "0";
                            }
                            else
                            {
                                dt.Rows[i][sIndex + "得分"] = (tmp * fs / max).ToString("0.0");
                            }
                        }
                    }
                }
                List<string> listCol = new List<string>();
                for (int i = 3; i < dt.Columns.Count; i++)
                {
                    if (sType == "1")
                    {
                        if (dt.Columns[i].ColumnName.IndexOf("得分") < 0)
                        {
                            listCol.Add(dt.Columns[i].ColumnName);
                        }
                    }
                    else
                    {
                        if (dt.Columns[i].ColumnName.IndexOf("得分") >= 0 ||
                            (dt.Columns[i].ColumnName.IndexOf("节能") >= 0 && dt.Columns[i].ColumnName.IndexOf("目标") >= 0) ||
                            (dt.Columns[i].ColumnName.IndexOf("节能") >= 0 && dt.Columns[i].ColumnName.IndexOf("状态") >= 0) ||
                            (dt.Columns[i].ColumnName.IndexOf("使用") >= 0 && dt.Columns[i].ColumnName.IndexOf("目标") >= 0) ||
                            (dt.Columns[i].ColumnName.IndexOf("使用") >= 0 && dt.Columns[i].ColumnName.IndexOf("状态") >= 0))
                        {
                            listCol.Add(dt.Columns[i].ColumnName);
                        }
                    }
                }
                foreach (string tmp in listCol)
                {
                    dt.Columns.Remove(tmp);
                }
                return dt.DefaultView;
            }
        }

        /// <summary>
        /// 获取选择表的类型 0：单表 >0：表示多表
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public string GetTableType(string sTable)
        {
            return SQLHelper.ExecuteString("select count(RT_ID) from tb_ReportTemplate where FieldParent='" + sTable.Split('|')[0] + "' and FieldChildCount>0 ");
        }

        public DataView QueryMTableField(string sAreas, string sStartYears, string sEndYears, string sFields, string sTable, string sType, bool bTotal)
        {
            string sql = String.Empty;
            string sAreaIDs = String.Empty;
            string[] aryArea = sFields.Split(',');
            string childlist = String.Empty;
            for (int i = 0; i < aryArea.Length; i++)
            {
                childlist += String.IsNullOrEmpty(childlist) ? "'" + aryArea[i].Split('^')[1] + "'" : ",'" + aryArea[i].Split('^')[1] + "'";
            }
            string[] aryList = { null, null };
            string sQueryField = String.Empty;
            string sQueryFieldTotal = String.Empty;
            //处理同比时间
            string sLastStartYears = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");
            string sLastEndYears = DateTime.Parse(sEndYears.Substring(0, 4) + "-" + sEndYears.Substring(4, 2) + "-01").AddYears(-1).ToString("yyyyMM");
            //------------修正环比算法-------------//
            //string sLastStartMonth = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01").AddMonths(-1).ToString("yyyyMM");
            //string sLastEndMonth = DateTime.Parse(sEndYears.Substring(0, 4) + "-" + sEndYears.Substring(4, 2) + "-01").AddMonths(-1).ToString("yyyyMM");
            int hbmonth = Math.Abs((int.Parse(sEndYears.Substring(0, 4)) - int.Parse(sStartYears.Substring(0, 4))) * 12 + int.Parse(sEndYears.Substring(4, 2)) - int.Parse(sStartYears.Substring(4, 2)));
            string sLastEndMonth = DateTime.Parse(sStartYears.Substring(0, 4) + "-" + sStartYears.Substring(4, 2) + "-01").AddMonths(-1).ToString("yyyyMM");
            string sLastStartMonth = DateTime.Parse(sLastEndMonth.Substring(0, 4) + "-" + sLastEndMonth.Substring(4, 2) + "-01").AddMonths(-hbmonth).ToString("yyyyMM");
            //------------修正环比算法-------------//
            //1.获取多表中的多个表 和其对应的数据字段
            DataView tableDv = SQLHelper.ExecuteDataView("select RT_ID,FieldName from tb_ReportTemplate where FieldName='" + sTable.Split('|')[1] + "'");
            DataView tmpdv = SQLHelper.ExecuteDataView("select * from tb_ReportTemplate where FieldParent='" + tableDv[0]["RT_ID"] + "' and FieldName in (" + childlist + ")  and FieldChildCount>0");
            List<string> sqlList = new List<string>();
            //通过left join 连接多个子表
            String joinTable = String.Empty;
            String firstTable = String.Empty;
            for (int x = 0; x < tmpdv.Count; x++)
            {
                if (String.IsNullOrEmpty(joinTable))
                {
                    joinTable = "[rt_" + tmpdv[x]["FieldName"].ToString() + "]";
                    firstTable = "rt_" + tmpdv[x]["FieldName"].ToString();
                    sQueryField = "[" + firstTable + "].[Years] as 年月,Area.[Name] as 地区";
                    sQueryFieldTotal = ",max(Area.[Name]) as 地区";
                }
                else
                {
                    joinTable += " left join rt_" + tmpdv[x]["FieldName"].ToString() + " on [" + firstTable + "].Area_ID=[rt_" + tmpdv[x]["FieldName"].ToString() + "].Area_ID and [" + firstTable + "].Years=[rt_" + tmpdv[x]["FieldName"].ToString() + "].Years ";
                }
                DataView myfield = SQLHelper.ExecuteDataView("select * from tb_ReportTemplate where FieldParent='" + tmpdv[x]["RT_ID"] + "'");
                for (int y = 0; y < myfield.Count; y++)
                {
                    sQueryField += ",[rt_" + tmpdv[x]["FieldName"] + "].[" + myfield[y]["FieldName"] + "] as [" + tmpdv[x]["FieldAlias"] + "]";
                    sQueryFieldTotal += ",sum([rt_" + tmpdv[x]["FieldName"] + "].[" + myfield[y]["FieldName"] + "]) as [" + tmpdv[x]["FieldAlias"] + "]";
                }
            }
            foreach (string tmp in sAreas.Split(','))
            {
                aryList = tmp.Split('^');
                sAreaIDs += String.IsNullOrEmpty(sAreaIDs) ? aryList[0] : "," + aryList[0];
            }
            //判断是否是累计状态
            if (!bTotal)
            {
                //判断是同比 环比 还是默认
                switch (sType)
                {
                    case "0":
                        sql = "select " + sQueryField + " from " + joinTable + " left join Area on [" + firstTable + "].Area_ID=Area.ID " +
                            "where [" + firstTable + "].Years>='" + sStartYears + "'and [" + firstTable + "].Years<='" + sEndYears + "' and [" + firstTable + "].Area_ID in (" + sAreaIDs + ") " +
                            "";
                        break;
                    case "1":
                        sql = "select " + sQueryField + " from " + joinTable + " left join Area on [" + firstTable + "].Area_ID=Area.ID " +
                            "where (([" + firstTable + "].Years>='" + sStartYears + "'and [" + firstTable + "].Years<='" + sEndYears + "') or ([" + firstTable + "].Years>='" + sLastStartYears + "'and [" + firstTable + "].Years<='" + sLastEndYears + "')) and [" + firstTable + "].Area_ID in (" + sAreaIDs + ") " +
                            "";
                        break;
                    case "2":
                        sql = "select " + sQueryField + " from " + joinTable + " left join Area on [" + firstTable + "].Area_ID=Area.ID " +
                            "where (([" + firstTable + "].Years>='" + sStartYears + "'and [" + firstTable + "].Years<='" + sEndYears + "') or ([" + firstTable + "].Years>='" + sLastStartMonth + "'and [" + firstTable + "].Years<='" + sLastEndMonth + "')) and [" + firstTable + "].Area_ID in (" + sAreaIDs + ") " +
                            "";
                        break;
                }
            }
            else
            {
                switch (sType)
                {
                    case "0":
                        sql = "select '" + sStartYears + "至" + sEndYears + "' as 年月" + sQueryFieldTotal + " from " + joinTable + " left join Area on [" + firstTable + "].Area_ID=Area.ID " +
                            "where [" + firstTable + "].Years>='" + sStartYears + "'and [" + firstTable + "].Years<='" + sEndYears + "' and [" + firstTable + "].Area_ID in (" + sAreaIDs + ") " +
                            "group by [" + firstTable + "].Area_ID";
                        break;
                    case "1":
                        sql = "select '" + sStartYears + "至" + sEndYears + "' as 年月" + sQueryFieldTotal + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where [" + firstTable + "].Years>='" + sStartYears + "'and [" + firstTable + "].Years<='" + sEndYears + "' and [" + firstTable + "].Area_ID in (" + sAreaIDs + ") " +
                            "group by [" + firstTable + "].Area_ID " +
                            "union " +
                            "select '" + sLastStartYears + "至" + sLastEndYears + "' as 年月" + sQueryFieldTotal + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where [" + firstTable + "].Years>='" + sLastStartYears + "'and [" + firstTable + "].Years<='" + sLastEndYears + "' and [" + firstTable + "].Area_ID in (" + sAreaIDs + ") " +
                            "group by [" + firstTable + "].Area_ID ";
                        break;
                    case "2":
                        sql = "select '" + sStartYears + "至" + sEndYears + "' as 年月" + sQueryFieldTotal + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where [" + firstTable + "].Years>='" + sStartYears + "'and [" + firstTable + "].Years<='" + sEndYears + "' and [" + firstTable + "].Area_ID in (" + sAreaIDs + ") " +
                            "group by [" + firstTable + "].Area_ID " +
                            "union " +
                            "select '" + sLastStartMonth + "至" + sLastEndMonth + "' as 年月" + sQueryFieldTotal + " from [" + sTable + "] left join Area on [" + sTable + "].Area_ID=Area.ID " +
                            "where [" + firstTable + "].Years>='" + sLastStartMonth + "'and [" + firstTable + "].Years<='" + sLastEndMonth + "' and [" + firstTable + "].Area_ID in (" + sAreaIDs + ") " +
                            "group by [" + firstTable + "].Area_ID";
                        break;
                }
            }
            return SQLHelper.ExecuteDataView(sql);
        }
    }
}

